import sqlite3
from datetime import datetime, date, timedelta
import os
import random
import pytz

class Database:
    def __init__(self):
        # 确保数据目录存在
        data_dir = os.path.join(os.path.dirname(os.path.dirname(__file__)), "data")
        os.makedirs(data_dir, exist_ok=True)
        
        # 数据库文件路径
        self.db_path = os.path.join(data_dir, "typing_history.db")
        
        # 初始化数据库
        self.init_db()
    
    def init_db(self):
        """初始化数据库表"""
        with sqlite3.connect(self.db_path) as conn:
            cursor = conn.cursor()
            
            # 创建练习记录表
            cursor.execute('''
                CREATE TABLE IF NOT EXISTS practice_records (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    word TEXT NOT NULL,
                    word_pos TEXT,
                    word_meaning TEXT,
                    is_correct BOOLEAN NOT NULL,
                    typing_time FLOAT NOT NULL,
                    wpm INTEGER NOT NULL,
                    practice_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                )
            ''')
            
            conn.commit()
    
    def add_record(self, word, pos, meaning, is_correct, typing_time, wpm):
        """添加练习记录"""
        with sqlite3.connect(self.db_path) as conn:
            cursor = conn.cursor()
            cursor.execute('''
                INSERT INTO practice_records 
                (word, word_pos, word_meaning, is_correct, typing_time, wpm, practice_time)
                VALUES (?, ?, ?, ?, ?, ?, datetime('now', 'localtime'))
            ''', (word, pos, meaning, 1 if is_correct else 0, typing_time, wpm))
            conn.commit()
    
    def get_today_stats(self):
        """获取今日统计数据"""
        with sqlite3.connect(self.db_path) as conn:
            cursor = conn.cursor()
            
            # 获取基本统计数据
            cursor.execute('''
                SELECT 
                    COUNT(*) as total_words,
                    SUM(CASE WHEN is_correct = 1 THEN 1 ELSE 0 END) as correct_words,
                    SUM(CASE WHEN is_correct = 0 THEN 1 ELSE 0 END) as wrong_words,
                    AVG(CASE WHEN is_correct = 1 THEN 1.0 ELSE 0.0 END) * 100 as accuracy,
                    AVG(wpm) as avg_wpm,
                    SUM(typing_time) as total_time
                FROM practice_records
                WHERE DATE(practice_time) = DATE('now', 'localtime')
            ''')
            
            row = cursor.fetchone()
            
            # 如果没有数据，返回默认值
            if not row or row[0] == 0:
                return {
                    'total_words': 0,
                    'correct_words': 0,
                    'wrong_words': 0,
                    'accuracy': 0.0,
                    'avg_wpm': 0,
                    'total_time': 0
                }
            
            return {
                'total_words': row[0],
                'correct_words': row[1] or 0,
                'wrong_words': row[2] or 0,
                'accuracy': row[3] or 0.0,
                'avg_wpm': int(row[4] or 0),
                'total_time': int(row[5] or 0)
            }

    def get_most_error_words(self):
        """获取今日错误最多的单词"""
        with sqlite3.connect(self.db_path) as conn:
            cursor = conn.cursor()
            cursor.execute('''
                SELECT word, COUNT(*) as error_count
                FROM practice_records
                WHERE is_correct = 0 
                AND DATE(practice_time) = DATE('now', 'localtime')
                GROUP BY word
                ORDER BY error_count DESC
                LIMIT 5
            ''')
            
            return [{'word': row[0], 'count': row[1]} for row in cursor.fetchall()]

    def get_daily_stats(self, date_str):
        """获取指定日期的统计数据"""
        # 转换日期字符串为东八区时间
        tz = pytz.timezone('Asia/Shanghai')
        date = datetime.strptime(date_str, '%Y-%m-%d')
        date = tz.localize(date)
        
        # 获取该日期的开始和结束时间
        start_time = date.replace(hour=0, minute=0, second=0, microsecond=0)
        end_time = date.replace(hour=23, minute=59, second=59, microsecond=999999)
        
        with sqlite3.connect(self.db_path) as conn:
            cursor = conn.cursor()
            
            cursor.execute('''
                SELECT 
                    COUNT(*) as total_words,
                    SUM(CASE WHEN is_correct = 1 THEN 1 ELSE 0 END) as correct_words,
                    SUM(CASE WHEN is_correct = 0 THEN 1 ELSE 0 END) as wrong_words,
                    AVG(CASE WHEN is_correct = 1 THEN 1.0 ELSE 0.0 END) * 100 as accuracy,
                    AVG(wpm) as avg_wpm,
                    SUM(typing_time) as total_time
                FROM practice_records
                WHERE DATE(practice_time) = ?
            ''', (date_str,))
            
            row = cursor.fetchone()
            
            stats = {
                'total_words': row[0] or 0,
                'correct_words': row[1] or 0,
                'wrong_words': row[2] or 0,
                'accuracy': row[3] or 0.0,
                'avg_wpm': row[4] or 0,
                'total_time': row[5] or 0
            }
            
            return stats

    def get_daily_records(self, date_str):
        """获取指定日期的练习记录"""
        with sqlite3.connect(self.db_path) as conn:
            cursor = conn.cursor()
            
            cursor.execute('''
                SELECT 
                    word,
                    word_pos,
                    word_meaning,
                    is_correct,
                    typing_time,
                    wpm,
                    practice_time
                FROM practice_records
                WHERE DATE(practice_time) = ?
                ORDER BY practice_time DESC
            ''', (date_str,))
            
            return cursor.fetchall()

    def get_weekly_stats(self):
        """获取近7天的统计数据"""
        # 设置东八区时区
        tz = pytz.timezone('Asia/Shanghai')
        now = datetime.now(tz)
        
        # 获取7天前的日期（使用本地时间）
        seven_days_ago = now - timedelta(days=6)
        seven_days_ago = seven_days_ago.replace(hour=0, minute=0, second=0, microsecond=0)
        
        dates = []
        total_counts = []
        correct_counts = []
        
        # 生成7天的日期列表
        for i in range(7):
            current_date = seven_days_ago + timedelta(days=i)
            dates.append(current_date)
            
            # 获取当天的统计数据
            stats = self.get_daily_stats(current_date.strftime('%Y-%m-%d'))
            if stats:
                total_counts.append(stats['total_words'])
                correct_counts.append(stats['accuracy'])
            else:
                total_counts.append(0)
                correct_counts.append(0)
        
        return dates, total_counts, correct_counts

    def add_test_data(self):
        """添加测试数据"""
        with sqlite3.connect(self.db_path) as conn:
            cursor = conn.cursor()
            
            # 获取今天的日期
            today = datetime.now().date()
            
            # 准备测试数据（最近7天）
            test_data = []
            for i in range(7):
                date = today - timedelta(days=i)
                # 每天添加一些随机数据
                for j in range(5):  # 每天5条记录
                    test_data.append((
                        f'word{j}',  # 单词
                        'n.',  # 词性
                        f'meaning{j}',  # 含义
                        random.choice([0, 1]),  # 随机正确或错误
                        random.randint(1, 10),  # 随机用时
                        random.randint(20, 80),  # 随机WPM
                        date.strftime('%Y-%m-%d %H:%M:%S')  # 日期时间
                    ))
            
            # 插入测试数据
            cursor.executemany('''
                INSERT INTO practice_records 
                (word, word_pos, word_meaning, is_correct, typing_time, wpm, practice_time)
                VALUES (?, ?, ?, ?, ?, ?, ?)
            ''', test_data)
            
            conn.commit()
